# -*- coding:utf-8 -*-

import requests
import json
from datetime import datetime, timedelta
import pymssql
import pandas as pd
import os
import base64
import time


requests.packages.urllib3.disable_warnings()  # 忽略HTTPS安全警告


"""
https://yzzs.kdy100.com/  16
18546683115 zaqxsw123
packStatus  outType   
1               0  已出库 取件
1               1   已出库 退件
0               ""  待出库
companyId
100 中通快递
101 申通快递
102 圆通快递
103 韵达快递
104 百世快递
107 邮政快递
"""

class ZhuShou():
    def __init__(self):
        # self.conn, self.cursor = mssql_ini()
        self.headers = {
            'Accept': 'application/json, text/plain, */*',
            'Accept-Encoding': 'gzip, deflate, br',
            'Accept-Language': 'zh-CN,zh;q=0.9',
            'Authorication': 'eyJhbGciOiJIUzI1NiJ9.eyJleHAiOjE1OTIzOTk4MDIsInN1YiI6IjBkOGM4MTI1NDE0MzY0MzYzN2U0N2JhMjM4Y2RlYzU2IiwicGhvbmVOdW0iOiIxODU0NjY4MzExNSIsImlzcyI6IlAwNmIzNjVjMjNlLTliY2MtNGU3MC1hZDNmLTkwOGNmZDhjMjNiMCJ9.X0uXUVytKs7n6kpHB66ES45DNTYgj9knQ0-XPVy1d3A',
            'Connection': 'keep-alive',
            'Content-Type': 'application/json;charset=UTF-8',
            'DNT': '1',
            'Host': 'yzzs.kdy100.com:1991',
            'mossctx-clientType': 'WEB',
            'mossctx-versionCode': '1090302',
            'Origin': 'https://yzzs.kdy100.com',
            'Referer': 'https://yzzs.kdy100.com/estation/index.html',
            'Sec-Fetch-Dest': 'empty',
            'Sec-Fetch-Mode': 'cors',
            'Sec-Fetch-Site': 'same-site',
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.61 Safari/537.36'
        }

    # 数据导出-历史数据
    def get_history(self, res):
        try:
            startTime = (datetime.now()).strftime("%Y-%m-%d") + " 00:00:00"
            endTime = (datetime.now() + timedelta(days=1)).strftime("%Y-%m-%d") + " 00:00:00"
            postData = f'{{"userId":"P06b365c23e-9bcc-4e70-ad3f-908cfd8c23b0","startTime":"{startTime}","endTime":"{endTime}","companyId":"","packStatus":"","stockDay":"","notifyStatus":""}}'
            url = f"https://yzzs.kdy100.com:1991/mossapi/mossp.expressj/downPackInfo"
            html = requests.post(url, headers=self.headers,  data=postData, verify=False)
            data_json = json.loads(html.text)
            taskId = data_json['taskId']
            retCode = data_json['retCode']
            if taskId != "":
                print(f"============获取：【{taskId}】=============")

                # 获取excel下载地址
                postData1 = f'{{"userId":"P06b365c23e-9bcc-4e70-ad3f-908cfd8c23b0","taskId":"{taskId}"}}'
                url_down = "https://yzzs.kdy100.com:1991/mossapi/mossp.expressj/getDownPackInfo"
                html_down = requests.post(url_down, headers=self.headers, data=postData1, verify=False)
                print(f"============获取：【下载地址】=============")
                xls_url = json.loads(html_down.text)['downInfo']['downUrl']
                if xls_url.find("xlsx") < 0:
                    html_down = requests.post(url_down, headers=self.headers, data=postData1, verify=False)
                    xls_url = json.loads(html_down.text)['downInfo']['downUrl']
                headers1 = {
                    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.61 Safari/537.36'
                }

                file_name = "./" + "history.xlsx"
                with open(file_name, "wb") as f:
                    xls_content = requests.get(xls_url, headers=headers1, verify=False).content
                    f.write(xls_content)
                print(f"============【Excel下载完成】=============")
                self.insert_history()
            else:
                print(f"============没有要导出的记录=============")
        except Exception as ex:
            # self.log_info("数据导出-历史数据", ex)
            print(str(ex))

    # 数据导出-快递
    def get_kd(self, res):
        try:
            startTime = (datetime.now() + timedelta(days=-90)).strftime("%Y-%m-%d") + " 00:00:00"
            endTime = (datetime.now() + timedelta(days=1)).strftime("%Y-%m-%d") + " 00:00:00"
            postData = f'{{"userId":"P06b365c23e-9bcc-4e70-ad3f-908cfd8c23b0","startTime":"{startTime}","endTime":"{endTime}","packStatus":"3","companyId":"","notifyStatus":"","stockDay":"","mailNum":"","callee":"","packNum":"","sortBy":2,"sortOrder":"desc","currentPage":1,"pageSize":40}}'
            url = f"https://yzzs.kdy100.com:1991/mossapi/mossp.expressj/queryExpressListV2"
            html = requests.post(url, headers=self.headers, data=postData, verify=False)
            print(f"============获取【快递管理】信息=============")
            data_json = json.loads(html.text)
            for d in data_json['expressPackLst']:
                company = self.get_company(d['companyId'])  # 快递公司
                packNum = d['packNum']  # 货号
                callee = d['callee']  # 联系电话
                mailNum = d['mailNum']  # 运单号
                status = self.get_status(d['packStatus'], d['outType'])  # 快递状态 0  1已出库(取件) 2已出库(取件)
                inTime = d['inTime']  # 入库时间
                stockDay = d['stockDay']  # 在库天数
                if d['outType'] == "":
                    outTime = "未出库"  # 出库时间
                else:
                    outTime = d['outTime']  # 出库时间
                notifyStatus = self.get_notifyStatus(d['notifyType'])  # 通知状态 0 未通知 1 不通知 2 已通知 3 已回复
                notifyTime = d['notifyTime']  # 通知时间
                remark = d['remark']  # 备注
                uploadStatusDesc = d['uploadStatusDesc']  # 上传状态
                # 查询是否有此单号，没有，保存。有，更新快递状态
                select_sql = f"select mailNum from t_history where mailNum='{mailNum}'"
                self.cursor.execute(select_sql)
                if self.cursor.fetchone() is None:
                    # 插入数据
                    insert_sql = f"insert into t_history (company,mailNum,callee,packNum,status,inTime,notifyStatus,notifyTime," \
                                 f"stockDay,outTime,remark,uploadStatusDesc) values('{company}','{mailNum}','{callee}','{packNum}','{status}','{inTime}','{notifyStatus}','{notifyTime}'," \
                                 f"'{stockDay}','{outTime}','{remark}','{uploadStatusDesc}')"

                    self.cursor.execute(insert_sql)
                    result_proc = self.cursor.callproc("sp_ChkCusData", (company, mailNum, callee, "",
                                                                              packNum,status, inTime,
                                                                              notifyStatus, notifyTime,
                                                                              stockDay, outTime, remark))
                    self.conn.commit()
                    print(f"============单号：【{mailNum}】数据【保存】=============")
                else:
                    update_sql = f"update t_history set status='{status}' where mailNum='{mailNum}'"
                    self.cursor.execute(update_sql)
                    result_proc = self.cursor.callproc("sp_ChkCusData", (company, mailNum, callee, "",
                                                                              packNum,status, inTime,
                                                                              notifyStatus, notifyTime,
                                                                              stockDay, outTime, remark))
                    self.conn.commit()
                    print(f"============单号：【{mailNum}】数据【更新】=============")
            # self.cursor.close()
            # self.conn.close()
            print("============【快递管理】处理完成=============")
        except Exception as ex:
            self.log_info("数据导出-快递管理", ex)

    # 获取快递公司名称
    def get_company(self, companyId):
        company = ""
        if companyId == 100:
            company = "中通快递"
        elif companyId == 101:
            company = "申通快递"
        elif companyId == 102:
            company = "圆通快递"
        elif companyId == 103:
            company = "韵达快递"
        elif companyId == 104:
            company = "百世快递"
        elif companyId == 107:
            company = "邮政快递"

        return company

    # 获取状态
    def get_status(self, packStatus, outType):
        status = ""
        if packStatus == "1" and outType == "0":
            status = "已出库(取件)"
        elif packStatus == "1" and outType == "1":
            status = "已出库(退件)"
        elif packStatus == "0" and outType == "":
            status = "待出库"
        else:
            status = ""

        return status

    # 获取通知状态
    def get_notifyStatus(self, notifyStatus):
        notify = ""
        if notifyStatus == "0":
            notify = "未通知"
        elif notifyStatus == "1":
            notify = "不通知"
        elif notifyStatus == "2":
            notify = "已通知"
        elif notifyStatus == "3":
            notify = "已回复"

        return notify

    # 保存数据到MS SERVER
    def insert_history(self):
        # for fi in os.listdir('./'):
        #     if fi.split(".")[1] == "xlsx":
        try:
            print(f"============读取Excel数据=============")
            df = pd.read_excel('./history.xlsx')
            # 行遍历
            for index, row in df.iterrows():
                company = row['快递公司']
                mailNum = row['运单号']
                callee = row['联系电话']
                lxr = str(row['联系人'])
                packNum = row['货号']
                status = row['包裹状态']
                inTime = row['入库时间']
                if str(inTime) == "nan":
                    inTime = ""
                notifyStatus = row['通知状态']
                notifyTime = row['通知时间']
                if str(notifyTime) == "nan":
                    notifyTime = ""
                stockDay = row['在库天数']
                outTime = row['出库时间']
                if str(outTime) == "nan":
                    outTime = ""
                remark = str(row['备注'])

                select_sql = f"select mailNum from t_history where mailNum='{mailNum}'"
                self.cursor.execute(select_sql)
                if self.cursor.fetchone() is None:
                    # 插入数据
                    insert_sql = f"insert into t_history (company,mailNum,callee,lxr,packNum,status,inTime,notifyStatus,notifyTime," \
                                 f"stockDay,outTime,remark) values('{company}','{mailNum}','{callee}','{lxr}','{packNum}','{status}','{inTime}','{notifyStatus}','{notifyTime}'," \
                                 f"'{stockDay}','{outTime}','{remark}')"
                    self.cursor.execute(insert_sql)
                    result_proc = self.cursor.callproc("sp_ChkCusData", (company, mailNum, callee, lxr,
                                                                              packNum,status, inTime,
                                                                              notifyStatus, notifyTime,
                                                                              stockDay, outTime, remark))
                    self.conn.commit()
                    print(f"============单号：【{mailNum}】数据保存=============")
                else:
                    # print(f"============单号：【{mailNum}】已存在！=============")
                    update_sql = f"update t_history set status='{status}' where mailNum='{mailNum}'"
                    self.cursor.execute(update_sql)
                    result_proc = self.cursor.callproc("sp_ChkCusData", (company, mailNum, callee, lxr,
                                                                              packNum,status, inTime,
                                                                              notifyStatus, notifyTime,
                                                                              stockDay, outTime, remark))
                    self.conn.commit()
                    print(f"============单号：【{mailNum}】数据【更新】=============")
            # self.cursor.close()
            # self.conn.close()
            # 删除下载的excel文件
            os.remove('./history.xlsx')
            print("============【历史数据】处理完成=============")
        except Exception as ex:
            self.log_info("保存数据-MS SQL Server", ex)
            error_str = f"快递公司:{company}==运单号:{mailNum}==联系电话:{callee}==联系人:{lxr}==货号:{packNum}==" \
                        f"包裹状态:{status}==入库时间:{inTime}==通知状态:{notifyStatus}==通知时间:{notifyTime}==" \
                        f"在库天数:{stockDay}==出库时间:{outTime}==备注:{remark}=="
            self.log_info("保存数据-MS SQL Server", error_str)


    # 保存日志
    def log_info(self, title, txt):
        file_name = "info_" + datetime.now().strftime("%Y-%m-%d")
        with open("./" + file_name + ".txt", "a", encoding="utf-8") as fp:
            fp.write(f'========={title}======={datetime.now().strftime("%Y-%m-%d %H:%M:%S")}========' + '\n')
            fp.write(str(txt) + '\n')

    def login(self):
        url = "https://yzzs.kdy100.com:1991/mossapi/mossp.expressj/ezpcourierLogin"
        postData = '{"phoneNum":"1854668311516","password":"95d333313c2bcab5cfd47834d3842d22"}'
        headers = {
            'Accept': 'application/json, text/plain, */*',
            'Accept-Encoding': 'gzip, deflate, br',
            'Accept-Language': 'zh-CN,zh;q=0.9',
            'Authorication': 'null',
            'Connection': 'keep-alive',
            'Content-Type': 'application/json;charset=UTF-8',
            'DNT': '1',
            'Host': 'yzzs.kdy100.com:1991',
            'mossctx-clientType': 'WEB',
            'mossctx-versionCode': '1090302',
            'Origin': 'https://yzzs.kdy100.com',
            'Referer': 'https://yzzs.kdy100.com/estation/index.html',
            'Sec-Fetch-Dest': 'empty',
            'Sec-Fetch-Mode': 'cors',
            'Sec-Fetch-Site': 'same-site',
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.116 Safari/537.36'
        }

        session = requests.Session()
        res = session.post(url, headers=headers, data=postData, verify=False)
        return session


# 连接数据库
def mssql_ini():
    server = "127.0.0.1"
    user = "sa"
    password = "sasa"
    print("============连接数据库=============")
    conn = pymssql.connect(server, user, password, database='db_LYUC8')
    cursor = conn.cursor()
    print("============数据库连接成功！=============")

    return conn, cursor



if __name__ == '__main__':
    zs = ZhuShou()
    res = zs.login()
    zs.get_history(res)
    # zs.insert_history()
    zs.get_kd(res)
    # flag_count = 0
    # while True:
    #     if datetime.now().second % 30 == 0:
    #         zs.get_kd()
    #         flag_count = 0
    #     elif datetime.now().minute % 5 == 0 and flag_count == 0:
    #         zs.get_history()
    #         flag_count += 1
    #     else:
    #         pass


